/**
 * 
 */
package training.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import training.GlobalProperties;
import training.data.Profile;
import training.data.User;

/**
 * @author Taras
 * 
 */
public class Connect {

	public static Connection getConnection() throws Exception {
		Connection conn = null;

		Class.forName(GlobalProperties.DRIVER).newInstance();
		conn = DriverManager.getConnection(GlobalProperties.URL
				+ GlobalProperties.DB, GlobalProperties.USER,
				GlobalProperties.PASS);
		// System.err.println("Database connection established");
		return conn;
	}

	public static void insertUser(Connection conn, User us) {
		PreparedStatement ps;
		Statement st;
		 String select = "select * from users order by id desc";
		try {
			ps = conn.prepareStatement(GlobalProperties.INSERT_USERS);
			st = conn.createStatement();
			ps.setInt(1, us.getID_Role());
			ps.setString(2, us.getFullname());
			ps.setString(3, us.getLogin());
			ps.setString(4, us.getPassword());
			ps.setString(5, us.getEmail());
			ps.setDate(6, us.getDate_registr());
			ps.setBoolean(7, us.isStatus());
			int count = ps.executeUpdate();
//			if(count > 0){
//				st.executeQuery(select);
//				rs = st.getResultSet();
//				rs.next();
//				String id = rs.getString(1);
//
//				
//				ps = connection.prepareStatement(insert);
//			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	public static void insertProfile(Connection conn, int id, Profile pr) {
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(GlobalProperties.INSERT_PROFILES);
			ps.setInt(1, id);
			ps.setString(2, pr.getLocation());
			ps.setString(3, pr.getPhoto());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	public static Integer findUserByLoginAndPass(Connection conn, User us) {
		Integer id_user = null;
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(GlobalProperties.SELECT_USER);
			ps.setString(1, us.getLogin());
			ps.setString(2, us.getPassword());
			ps.execute();
			ResultSet rs = ps.getResultSet();
			while (rs.next()) {
				id_user = rs.getInt(1);
			}

			// System.out.println(id_user);
			rs.close();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return id_user;
	}

	public static Profile findProfileByIdUser(Connection conn, int id) {
		Profile profile = new Profile();
		PreparedStatement ps;
		ResultSet rs;
		try {
			ps = conn.prepareStatement(GlobalProperties.SELECT_PROFILES);
			ps.setInt(1, id);
			ps.execute();
			rs = ps.getResultSet();
			while (rs.next()) {
				profile.setID(rs.getInt(1));
				profile.setID_User(rs.getInt(2));
				profile.setLocation(rs.getString(3));
				profile.setPhoto(rs.getString(4));
			}
			rs.close();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return profile;
	}

	public static void updateProfileByUserId(Connection conn, Profile pr, int id) {
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(GlobalProperties.UPDATE_PROFILES);
			ps.setString(1, pr.getLocation());
			ps.setString(2, pr.getPhoto());
			ps.setInt(3, id);
			ps.execute();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	// public static void main(String[] args) throws Exception {
	// java.sql.Date dd = new java.sql.Date(new java.util.Date().getTime());
	// User user1 = new User(dd, "email", "fullnamej",
	// 1,"login","password",true);
	// Profile pr = new Profile(1,"hkhgi","dgufufu");
	// Connection c = getConnection();
	// // insertUser(c, user1);
	// // insertProfile(c, 1, pr);
	// // System.out.println(findUserByLoginAndPass(c, user1)) ;
	// // System.out.println(count);
	// // Profile p = findProfileByIdUser(c, 1);
	// // System.out.println(p.getPhoto());
	// updateProfileByUserId(c, pr, 1);
	// }

}
